using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;

using EntLibEC.Cmn;

using EntLibEC.Info.Sale;
using EntLibEC.Info.Basic;

using EntLibEC.Dac;
using EntLibEC.Dac.Basic;
using EntLibEC.Dac.Sale;

using System.Transactions;

namespace EntLibEC.Biz.Sale
{
	/// <summary>
	/// Summary description for SaleGiftRelateMoneyManager.
	/// </summary>
	public class SaleGiftRelateMoneyManager
	{
		private SaleGiftRelateMoneyManager()
		{
		}
		private static SaleGiftRelateMoneyManager _instance;
		public static SaleGiftRelateMoneyManager GetInstance()
		{
			if(_instance==null)
			{
				_instance = new SaleGiftRelateMoneyManager();
			}
			return _instance;
		}
		private void map(SaleGiftRelateMoneyInfo oParam, DataRow tempdr)
		{
			oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
			oParam.CreateUserSysNo = Util.TrimIntNull(tempdr["CreateUserSysNo"]);
			oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
			oParam.GiftDesc = Util.TrimNull(tempdr["GiftDesc"]);
			oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]);
			oParam.MoneyStart = Util.TrimDecimalNull(tempdr["MoneyStart"]);
			oParam.MoneyEnd = Util.TrimDecimalNull(tempdr["MoneyEnd"]);
			oParam.Status = Util.TrimIntNull(tempdr["Status"]);
		}
		public void Insert(SaleGiftRelateMoneyInfo oParam)
		{
			new SaleGiftRelateMoneyDac().Insert(oParam);
		}
		public void Update(Hashtable paramHt)
		{
			new SaleGiftRelateMoneyDac().Update(paramHt);
		}
		public DataSet GetDs(Hashtable paramHash)
		{
			string sql = @"
							select a.*, username as createname, productid,productname
							from
								Ent_Sale_GiftRelateMoney a 
								inner join Ent_Sys_User on a.createusersysno = Ent_Sys_User.sysno
								inner join Ent_Product on a.productsysno = Ent_Product.sysno
							where
								1=1";

			if ( paramHash != null && paramHash.Count != 0 )
			{
				StringBuilder sb = new StringBuilder(100);
				foreach(string key in paramHash.Keys)
				{
					sb.Append(" and ");
					object item = paramHash[key];
					if ( key == "DateFromCreate")
					{
						sb.Append("a.CreateTime >= ").Append(Util.ToSqlString(item.ToString()));
					}
					else if ( key == "DateToCreate")
					{
						sb.Append("a.CreateTime <= ").Append(Util.ToSqlEndDate(item.ToString()));
					}
					else if ( key == "ProductSysNo")
					{
						sb.Append("a.ProductSysNo = ").Append(item.ToString());
					}
					else if ( key == "Status" )
					{
						sb.Append("a.Status = ").Append(item.ToString());
					}
					else if ( key == "ProductMoney" )
					{
						sb.Append(" ( MoneyStart<" + (decimal)(item) + " and MoneyEnd>=" + (decimal)item + ")");
					}
					else if ( item is int)
					{
						sb.Append(key).Append("=" ).Append(item.ToString());
					}
					else if ( item is string)
					{
						sb.Append(key).Append(" like ").Append(Util.ToSqlLikeString(item.ToString()));
					}
				}
				sql += sb.ToString();
			}
			else
			{
				sql = sql.Replace("select", "select top 50");
			}

			sql += " order by a.sysno desc";
			return SqlHelper.ExecuteDataSet(sql);
		}
		public SaleGiftRelateMoneyInfo Load(int sysno)
		{
			string sql = "select * from Ent_Sale_GiftRelateMoney where sysno=" + sysno;
			DataSet ds = SqlHelper.ExecuteDataSet(sql);
			if ( !Util.HasMoreRow(ds))
				return null;
			SaleGiftRelateMoneyInfo oInfo = new SaleGiftRelateMoneyInfo();
			map(oInfo, ds.Tables[0].Rows[0]);
			return oInfo;
		}

		public void Delete(int sysno)
		{
			new SaleGiftRelateMoneyDac().Delete(sysno);
		}

		public SortedList GetGiftListByMoney(decimal productMoney)
		{
			string sql= @"select a.sysno, productname, a.productsysno, a.giftdesc from 
							Ent_Sale_GiftRelateMoney a
							inner join Ent_Product on a.productsysno = Ent_Product.sysno
							inner join Ent_Inventory on a.productsysno = Ent_Inventory.productsysno
							where 
								Ent_Product.status = @online
							and availableqty+virtualqty>0
							and a.status = @valid
							and a.moneystart <= @productMoney and a.moneyend>@productMoney
							order by MoneyStart";
			sql = sql.Replace("@online", ((int)AppEnum.ProductStatus.Valid).ToString());
			sql = sql.Replace("@valid", ((int)AppEnum.BiStatus.Valid).ToString());
			sql = sql.Replace("@productMoney", productMoney.ToString());

			DataSet ds = SqlHelper.ExecuteDataSet(sql);
			if ( !Util.HasMoreRow(ds))
				return null;

			SortedList sl= new SortedList(5);
			foreach(DataRow dr in ds.Tables[0].Rows)
			{
				sl.Add(Util.TrimIntNull(dr["ProductSysNo"]), Util.TrimNull(dr["GiftDesc"]));
			}
			return sl;
		}
	}
}
